package com.school.sports.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.school.sports.entity.ExceptionRecord;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.time.LocalDateTime;
import java.util.List;
import java.util.Map;

/**
 * 异常记录数据访问接口
 */
@Mapper
public interface ExceptionRecordMapper extends BaseMapper<ExceptionRecord> {

    /**
     * 按异常类型统计
     * @param startTime 开始时间
     * @param endTime 结束时间
     * @return 统计结果
     */
    @Select("SELECT exception_type as type, COUNT(*) as count " +
            "FROM exception_record " +
            "WHERE deleted = 0 " +
            "AND (#{startTime} IS NULL OR occur_time >= #{startTime}) " +
            "AND (#{endTime} IS NULL OR occur_time <= #{endTime}) " +
            "GROUP BY exception_type " +
            "ORDER BY count DESC")
    List<Map<String, Object>> getExceptionStatisticsByType(
            @Param("startTime") LocalDateTime startTime,
            @Param("endTime") LocalDateTime endTime);

    /**
     * 按异常级别统计
     * @param startTime 开始时间
     * @param endTime 结束时间
     * @return 统计结果
     */
    @Select("SELECT exception_level as level, COUNT(*) as count " +
            "FROM exception_record " +
            "WHERE deleted = 0 " +
            "AND (#{startTime} IS NULL OR occur_time >= #{startTime}) " +
            "AND (#{endTime} IS NULL OR occur_time <= #{endTime}) " +
            "GROUP BY exception_level " +
            "ORDER BY level")
    List<Map<String, Object>> getExceptionStatisticsByLevel(
            @Param("startTime") LocalDateTime startTime,
            @Param("endTime") LocalDateTime endTime);

    /**
     * 按发生地点统计
     * @param startTime 开始时间
     * @param endTime 结束时间
     * @return 统计结果
     */
    @Select("SELECT occur_location as location, COUNT(*) as count " +
            "FROM exception_record " +
            "WHERE deleted = 0 " +
            "AND (#{startTime} IS NULL OR occur_time >= #{startTime}) " +
            "AND (#{endTime} IS NULL OR occur_time <= #{endTime}) " +
            "AND occur_location IS NOT NULL " +
            "GROUP BY occur_location " +
            "ORDER BY count DESC " +
            "LIMIT 20")
    List<Map<String, Object>> getExceptionStatisticsByLocation(
            @Param("startTime") LocalDateTime startTime,
            @Param("endTime") LocalDateTime endTime);

    /**
     * 按处理状态统计
     * @param startTime 开始时间
     * @param endTime 结束时间
     * @return 统计结果
     */
    @Select("SELECT handle_status as status, COUNT(*) as count " +
            "FROM exception_record " +
            "WHERE deleted = 0 " +
            "AND (#{startTime} IS NULL OR occur_time >= #{startTime}) " +
            "AND (#{endTime} IS NULL OR occur_time <= #{endTime}) " +
            "GROUP BY handle_status " +
            "ORDER BY status")
    List<Map<String, Object>> getExceptionStatisticsByStatus(
            @Param("startTime") LocalDateTime startTime,
            @Param("endTime") LocalDateTime endTime);

    /**
     * 获取异常趋势数据
     * @param startTime 开始时间
     * @param endTime 结束时间
     * @param interval 时间间隔
     * @return 趋势数据
     */
    @Select("SELECT " +
            "CASE " +
            "WHEN #{interval} = 'hour' THEN DATE_FORMAT(occur_time, '%Y-%m-%d %H:00:00') " +
            "WHEN #{interval} = 'day' THEN DATE_FORMAT(occur_time, '%Y-%m-%d') " +
            "WHEN #{interval} = 'week' THEN DATE_FORMAT(occur_time, '%Y-%u') " +
            "WHEN #{interval} = 'month' THEN DATE_FORMAT(occur_time, '%Y-%m') " +
            "ELSE DATE_FORMAT(occur_time, '%Y-%m-%d') " +
            "END as time_bucket, " +
            "COUNT(*) as count " +
            "FROM exception_record " +
            "WHERE deleted = 0 " +
            "AND (#{startTime} IS NULL OR occur_time >= #{startTime}) " +
            "AND (#{endTime} IS NULL OR occur_time <= #{endTime}) " +
            "GROUP BY time_bucket " +
            "ORDER BY time_bucket")
    List<Map<String, Object>> getExceptionTrendData(
            @Param("startTime") LocalDateTime startTime,
            @Param("endTime") LocalDateTime endTime,
            @Param("interval") String interval);
}